import datetime
import random
import time

import pandas as pd
import utils.utils
from app import app
from db.db import db
from db.models import (DriversServiceTable, DriverTable, OrderTable,
                       SimulationTable, StatesTable, TDTable, TimesTable,
                       StateStatisticTable)
from flask import request
from sqlalchemy import or_

# 存储进度数据
progress_data = 0.0


# 路由
@app.route('/')
def index():
    hello = 'hello world'
    return hello


@app.route('/order_statistic_month', methods=['post', 'get'])
def order_statistic_month():
    """
    功能：月-天尺度订单统计信息
    """
    sql = ''' select * from order_statistic; '''
    results = pd.read_sql(sql=sql, con=db.engine)
    results = results.to_json(orient="index")
    return results


@app.route('/order_statistic_day', methods=['post', 'get'])
def order_statistic_day():
    """
    功能：天-小时尺度订单统计信息
    """
    # res = request.get_json()  # 前端请求
    # date = res['date']  # 日期
    date = 20161101
    sql = f''' select * from order_statistic_{date}; '''
    results = pd.read_sql(sql=sql, con=db.engine)
    results = results.to_json(orient="index")
    return results


@app.route('/driver_statistic', methods=['post', 'get'])
def driver_statistic():
    """
    功能：司机统计信息
    输入：司机ID、日期
    """
    res = request.get_json()  # 前端请求
    date = res['date']
    tablename = f'driver_{date}'
    id_driver = res['id_driver']
    sql = f''' select * from {tablename} where id = '{id_driver}'; '''
    results = pd.read_sql(sql=sql, con=db.engine)
    results = results.to_json(orient="index")
    return results


@app.route('/driver_ordersequence')
def driver_ordersequence():
    """
    功能：司机订单序列
    输入：司机ID、日期
    """
    res = request.get_json()
    date = res['date']
    tablename = f'order_{date}'
    id_driver = res['id_driver']
    sql = f'''
        select "id","time_start","time_end","time_cost","reward"
            from {tablename}
            where "id_driver" = '{id_driver}';
        '''
    results = pd.read_sql(sql=sql, con=db.engine)
    results = results.to_json(orient='index')
    return results


@app.route('/state_top10')
def state_top10():
    """
    功能：top10起点与终点状态转移概率
    """
    # 返回结果为元组列表
    start_top10 = StateStatisticTable.query.with_entities(
        StateStatisticTable.gid, StateStatisticTable.start_count).order_by(
            StateStatisticTable.start_count.desc()).limit(10).all()
    end_top10 = StateStatisticTable.query.with_entities(
        StateStatisticTable.gid, StateStatisticTable.end_count).order_by(
            StateStatisticTable.end_count.desc()).limit(10).all()
    state_transfer = list()
    for start in start_top10:
        start = start[0]  # 从元组中取出数据
        for end in end_top10:
            end = end[0]
            sql = f'''
                select count(*) from order_20161101
                    where "id_state_start" = {start} and
                    "id_state_end" = {end};
                '''
            value = db.session.execute(sql).fetchone()
            if value:
                value = value[0]  # 从元组中取出数据
            dict_state_transfer = {'start':start,'end':end,'value':value}
            state_transfer.append(dict_state_transfer)
    return state_transfer


# 强化学习训练 #
@app.route('/sarsa', methods=['post', 'get'])
def sarsa():
    global progress_data  # 进度
    res = request.get_json()  # 前端请求
    reward_decay = res['reward_decay']  # 奖励衰减值
    learning_rate = res['learning_rate']  # 学习率
    # 获取所有司机
    sql = ''' select "id" from drivers order by "id"; '''
    df_drivers = pd.read_sql_query(sql, db.engine)
    # 获取所有状态
    sql = ''' select "gid","value","delta" from states order by "gid"; '''
    df_states = pd.read_sql_query(sql, db.engine, index_col='gid')
    # 遍历司机
    for i in range(len(df_drivers)):
        # 获取当前司机所有订单
        sql = f''' select "reward","states_id_start" from orders
            where "drivers_id" = '{df_drivers.loc[i,'id']}'; '''
        df_orders = pd.read_sql_query(sql, db.engine)
        # 遍历订单
        for j in range(len(df_orders)):
            # 当前订单状态
            df_state_current = df_states.query(
                f'gid == {df_orders.loc[j,"states_id_start"]}')
            # 预测值
            q_predict = df_state_current.loc[df_state_current.index, 'value']
            q_target = 0  # 目标值
            if j != len(df_orders) - 1:  # 不是最后一个订单
                df_state_next = df_states.query(
                    f'gid == {df_orders.loc[j,"states_id_start"]}')
                q_target = df_orders.loc[
                    j, 'reward'] + reward_decay * df_state_next.loc[
                        df_state_next.index, 'value']
            else:
                q_target = df_orders.loc[j, 'reward']
            delta = abs(learning_rate * (q_target - q_predict))  # 误差
            q_value = q_predict + learning_rate * (q_target - q_predict)  # Q值
            df_states.loc[df_state_current.index, 'value'] = q_value
            df_states.loc[df_state_current.index, 'delta'] = delta
        progress_data = i / len(df_drivers) * 100
    # 更新数据库
    states = StatesTable.query.order_by(StatesTable.gid).all()
    for state in states:
        state.value = df_states.loc[state.gid, 'value']
    db.session.commit()
    return '完成强化学习训练'


@app.route('/tdlearning')
def tdlearning():
    reward_decay = 0.9  # 奖励衰减值
    # 获取所有司机
    sql = ''' select "id" from drivers order by "id"; '''
    df_drivers = pd.read_sql_query(sql, db.engine)
    # 获取所有状态
    sql = ''' select "gid","value","delta","count" from tdlearning order by "gid"; '''
    df_states = pd.read_sql_query(sql, db.engine, index_col='gid')
    # 遍历司机
    for i in range(len(df_drivers)):
        # 获取当前司机所有订单
        sql = f''' select "reward","states_id_start" from orders
            where "drivers_id" = '{df_drivers.loc[i,'id']}'; '''
        df_orders = pd.read_sql_query(sql, db.engine)
        # 遍历订单
        for j in range(len(df_orders)):
            # 当前订单状态
            df_state_current = df_states.query(
                f'gid == {df_orders.loc[j,"states_id_start"]}')
            # 预测值
            q_predict = df_state_current.loc[df_state_current.index, 'value']
            q_target = 0  # 目标值
            if j != len(df_orders) - 1:  # 不是最后一个订单
                df_state_next = df_states.query(
                    f'gid == {df_orders.loc[j,"states_id_start"]}')
                q_target = df_orders.loc[
                    j, 'reward'] + reward_decay * df_state_next.loc[
                        df_state_next.index, 'value']
            else:
                q_target = df_orders.loc[j, 'reward']
            count = df_state_current.loc[df_state_current.index, 'count'] + 1
            delta = abs(1 / count * (q_target - q_predict))  # 误差
            q_value = q_predict + (1 / count * (q_target - q_predict))  # Q值
            df_states.loc[df_state_current.index, 'value'] = q_value
            df_states.loc[df_state_current.index, 'delta'] = delta
            df_states.loc[df_state_current.index, 'count'] = count
        print(i, '/', len(df_drivers))
    # 更新数据库
    states = TDTable.query.order_by(TDTable.gid).all()
    for state in states:
        state.value = df_states.loc[state.gid, 'value']
        state.delta = df_states.loc[state.gid, 'delta']
        state.count = int(df_states.loc[state.gid, 'count'])
    db.session.commit()
    return '完成'


# 模拟实验 #
@app.route('/simulation')
def simulation():
    """
    功能：派单模拟实验
    """

    # 初始化
    db.session.execute('truncate simulation')
    db.session.commit()
    timedelta = datetime.timedelta(minutes=2)  # 时间差
    datetime_start = datetime.datetime(year=2016,
                                       month=11,
                                       day=1,
                                       hour=0,
                                       minute=0)  # 开始时间(时间戳)
    datetime_end = datetime.datetime(year=2016,
                                     month=11,
                                     day=1,
                                     hour=23,
                                     minute=59,
                                     second=59)  # 结束时间(时间戳)
    datetime_current = datetime_start  # 当前时间(时间戳)
    # pandas获取所有司机
    sql = ''' select * from drivers_service order by "time_start"; '''
    df_drivers_all = pd.read_sql(sql=sql, con=db.engine, index_col='id')
    # pandas获取所有订单
    sql = ''' select "id","time_start","time_end","time_cost",
    "states_id_start","states_id_end","reward","order_state" from orders; '''
    df_orders_all = pd.read_sql(sql=sql, con=db.engine, index_col='id')

    # 开始模拟
    while datetime_current < datetime_end:
        count_success = 0  #成功匹配数
        datetime_next = datetime_current + timedelta  # 下个时段
        print(f'# {datetime_current} ~ {datetime_next} #')  # 打印进度
        # 获取待匹配订单
        df_orders_dispatch = df_orders_all.query(
            "time_start >= @datetime_current and time_start < @datetime_next and order_state == '待匹配'"
        )
        if len(df_orders_dispatch) == 0:
            datetime_current = datetime_next
            continue
        # 获取待匹配司机
        # 司机服务时间以半小时为单位
        time_start_service = datetime.datetime.time(datetime_current)
        if time_start_service.minute >= 30:  # xx:30:00 ~ xx:59:59
            time_start_service = datetime.time(hour=time_start_service.hour,
                                               minute=30,
                                               second=0)
        else:  # xx:00:00 ~ xx:29:59
            time_start_service = datetime.time(hour=time_start_service.hour,
                                               minute=0,
                                               second=0)
        df_drivers_dispatch = df_drivers_all.query(
            "time_start == @time_start_service and driver_state == '空闲'")
        # 遍历待匹配司机
        i = 1  # 进度
        for index, df_driver in df_drivers_dispatch.iterrows():
            # 3*3窗口搜索订单
            state_ids = utils.getStateIDs(df_driver['states_id'])
            sql_orders = ''
            if len(state_ids) == 4:
                sql_orders = f'(states_id_start == {state_ids[0]} or '+\
                f'states_id_start == {state_ids[1]} or '+\
                f'states_id_start == {state_ids[2]} or '+\
                f'states_id_start == {state_ids[3]}) '
            elif len(state_ids) == 6:
                sql_orders = f'(states_id_start == {state_ids[0]} or '+\
                f'states_id_start == {state_ids[1]} or '+\
                f'states_id_start == {state_ids[2]} or '+\
                f'states_id_start == {state_ids[3]} or '+\
                f'states_id_start == {state_ids[4]} or '+\
                f'states_id_start == {state_ids[5]}) '
            else:
                sql_orders = f'(states_id_start == {state_ids[0]} or '+ \
                f'states_id_start == {state_ids[1]} or '+ \
                f'states_id_start == {state_ids[2]} or '+ \
                f'states_id_start == {state_ids[3]} or '+ \
                f'states_id_start == {state_ids[4]} or '+ \
                f'states_id_start == {state_ids[5]} or '+ \
                f'states_id_start == {state_ids[6]} or '+ \
                f'states_id_start == {state_ids[7]} or '+ \
                f'states_id_start == {state_ids[8]}) '
            sql_orders += "and (time_start >= @datetime_current and time_start < @datetime_next) and order_state == '待匹配'"
            df_orders_window = df_orders_all.query(sql_orders)
            # 司乘匹配
            if len(df_orders_window) == 1:  # 范围内1个订单
                # 成功匹配数
                count_success += 1
                # 生成新的订单记录
                simulation = SimulationTable(
                    id=df_orders_window.index[0],
                    time_cost=df_orders_window.iloc[0]['time_cost'],
                    drivers_id=df_driver['drivers_id'],
                    reward=df_orders_window.iloc[0]['reward'])
                db.session.add(simulation)
                # 更新订单状态为“已匹配”
                df_orders_all.loc[df_orders_window.index[0],
                                  'order_state'] = '已匹配'
                # 更新司机状态改为“载客”
                df_drivers_service = df_drivers_all.query(
                    "drivers_id == '" + df_driver['drivers_id'] + "'")
                for id, df_driver_service in df_drivers_service.iterrows():
                    time_start = datetime.datetime.time(
                        df_orders_window.iloc[0]['time_start'])
                    time_end = datetime.datetime.time(
                        df_orders_window.iloc[0]['time_end'])
                    if (df_driver_service['time_start'] <= time_start<= \
                            df_driver_service['time_end']) or \
                        (df_driver_service['time_start'] <= time_end <= \
                            df_driver_service['time_end']) or \
                        (df_driver_service['time_start'] >= time_start and \
                            df_driver_service['time_end'] >= time_end):
                        df_drivers_all.loc[id, 'driver_state'] = '载客'
            elif len(df_orders_window) > 1:  # 范围内多个订单
                # 获取状态值最大的订单
                dispatchValueList = list()
                orderIDList = list()
                for id, df_order in df_orders_window.iterrows():
                    dispatchValue = utils.getDispatchValue(
                        df_order['states_id_start'], df_order['states_id_end'])
                    dispatchValueList.append(dispatchValue)
                    orderIDList.append(id)
                dispatchValue_max = max(zip(dispatchValueList, orderIDList))
                # 成功匹配数
                count_success += 1
                # 生成新的订单记录
                simulation = SimulationTable(
                    id=dispatchValue_max[1],
                    time_cost=df_orders_window.loc[dispatchValue_max[1],
                                                   'time_cost'],
                    drivers_id=df_driver['drivers_id'],
                    reward=df_orders_window.loc[dispatchValue_max[1],
                                                'reward'])
                db.session.add(simulation)
                # 更新订单状态为“已匹配”
                df_orders_all.loc[dispatchValue_max[1], 'order_state'] = '已匹配'
                # 将司机状态改为‘载客’
                df_drivers_service = df_drivers_all.query(
                    "drivers_id == '" + df_driver['drivers_id'] + "'")
                for id, df_driver_service in df_drivers_service.iterrows():
                    time_start = datetime.datetime.time(
                        df_orders_window.loc[dispatchValue_max[1],
                                             'time_start'])
                    time_end = datetime.datetime.time(
                        df_orders_window.loc[dispatchValue_max[1], 'time_end'])
                    if (df_driver_service['time_start'] <= time_start <= \
                            df_driver_service['time_end']) or \
                        (df_driver_service['time_start'] <= time_end <= \
                            df_driver_service['time_end']) or \
                        (df_driver_service['time_start'] >= time_start and \
                            df_driver_service['time_end'] >= time_end):
                        df_drivers_all.loc[id, 'driver_state'] = '载客'

            print(i, '/', len(df_drivers_dispatch))
            i += 1
        count_fail = len(df_orders_dispatch) - count_success
        print(
            f'{datetime_current} ~ {datetime_next}, 成功匹配数 = {count_success}, 失败匹配数 = {count_fail}'
        )
        db.session.commit()
        datetime_current = datetime_next
    return '完成模拟实验'


@app.route('/show_progress')
def show_progress():
    '''
    前端请求进度的函数
    '''
    datetime.datetime.timestamp()
    return {'progress': progress_data}


# 测试 #
@app.route('/test')
def test():
    return '测试成功'
